<?php


namespace App\Listeners;


use App\Models\ExcelFile;
use App\Models\Order;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class OrderUtils
{
    public static function getGYOrderRecode($where, $order_status, $excel_record_id, $branch_list)
    {
        try {
            $order_list = Order::where($where)->whereIn('order_status', $order_status)->whereIn('branch_id', $branch_list)->with('branch')->with('worker')->with('product')->with('user')->with('bank_card')->get();
            $now_date = date('Y-m-d', time());
            $spreadsheet = new Spreadsheet();
            $orderSheet = $spreadsheet->getActiveSheet();
            // 设置工作表名称
            $orderSheet->setTitle('订单表');
            // 设置表头
            $orderSheet->setCellValueByColumnAndRow(1, 1, '合同编号');      // A
            $orderSheet->setCellValueByColumnAndRow(2, 1, '客户姓名');       // B
            $orderSheet->setCellValueByColumnAndRow(3, 1, '客户手机号');     // C
            $orderSheet->setCellValueByColumnAndRow(4, 1, '签署日期');     // D
            $orderSheet->setCellValueByColumnAndRow(5, 1, '起收益日期');     // E
            $orderSheet->setCellValueByColumnAndRow(6, 1, '到期日期');      // F
            $orderSheet->setCellValueByColumnAndRow(7, 1, '产品名称');      // G
            $orderSheet->setCellValueByColumnAndRow(8, 1, '投资期限');      // H
            $orderSheet->setCellValueByColumnAndRow(9, 1, '合同金额');      // I
            $orderSheet->setCellValueByColumnAndRow(10, 1, '年化金额');      // J
            $orderSheet->setCellValueByColumnAndRow(11, 1, '每月汇款(取整)');     // K
            $orderSheet->setCellValueByColumnAndRow(12, 1, '每月汇款');      // L
            $orderSheet->setCellValueByColumnAndRow(13, 1, '年化收益率(%)');     // M
            $orderSheet->setCellValueByColumnAndRow(14, 1, '年化比例');       // N
            $orderSheet->setCellValueByColumnAndRow(15, 1, '客户类型');     // O
            $orderSheet->setCellValueByColumnAndRow(16, 1, '合同类型');          // P
            $orderSheet->setCellValueByColumnAndRow(17, 1, '原合同');     // Q
            $orderSheet->setCellValueByColumnAndRow(18, 1, '身份证');           // R
            $orderSheet->setCellValueByColumnAndRow(19, 1, '客户生日');      // S
            $orderSheet->setCellValueByColumnAndRow(20, 1, '开户行');         // T
            $orderSheet->setCellValueByColumnAndRow(21, 1, '银行卡号');         // U
            $orderSheet->setCellValueByColumnAndRow(22, 1, '直属部门(三级)');      // V
            $orderSheet->setCellValueByColumnAndRow(23, 1, '直属部门(四级)');           // W
            $orderSheet->setCellValueByColumnAndRow(24, 1, '直属部门(五级)');       // X
            $orderSheet->setCellValueByColumnAndRow(25, 1, '部门');                 // Y
            $orderSheet->setCellValueByColumnAndRow(26, 1, '经办人');                   // Z
            $orderSheet->setCellValueByColumnAndRow(27, 1, '经办人电话');       // AA
            $orderSheet->setCellValueByColumnAndRow(28, 1, '公司');                 // AB
            $orderSheet->setCellValueByColumnAndRow(29, 1, '状态');                 // AC
            $orderSheet->setCellValueByColumnAndRow(30, 1, '发送状态');                   // AD
            $row = 2;
            foreach ($order_list as $order) {
                if ($row > 20000) {
                    break;
                }
                $contract_no = $order['contract_no'] ?? '';      // 合同号
                $orderSheet->setCellValueByColumnAndRow(1, $row, $contract_no);
                $user_name = $order['user']['name'];       // 投资人姓名
                $orderSheet->setCellValueByColumnAndRow(2, $row, $user_name);       // B
                $user_num = $order['user']['contact_manner'] . "\t";       // 投资人联系方式
                $orderSheet->setCellValueByColumnAndRow(3, $row, $user_num);     // C
                $sign_time = $order['sign_time'];  // 签署日期
                //                $orderSheet->setCellValueByColumnAndRow(4, $row, date('Y/m/d', strtotime($sign_time)));     // E
                $sign_year = (int)(date('Y', strtotime($sign_time)));
                $sign_month = (int)(date('m', strtotime($sign_time)));
                $sign_day = (int)(date('d', strtotime($sign_time)));
                $orderSheet->setCellValueByColumnAndRow(4, $row, $sign_year . '/' . $sign_month . '/' . $sign_day);     // D
                //                $orderSheet->setCellValue('D'.$row, Date::PHPToExcel(strtotime($sign_time)));     // D
                //                $orderSheet->getStyle('D'.$row)->getNumberFormat()->setFormatCode(strtotime($sign_time));
                $valid_time = $order['valid_time'];  // 起收益日期
                $valid_year = (int)(date('Y', strtotime($valid_time)));
                $valid_month = (int)(date('m', strtotime($valid_time)));
                $valid_day = (int)(date('d', strtotime($valid_time)));
                $orderSheet->setCellValueByColumnAndRow(5, $row, $valid_year . '/' . $valid_month . '/' . $valid_day);     // E
                $contract_end_date = $order['contract_end_date'] ?? '';   // 到期时间
                $contract_end_year = (int)(date('Y', strtotime($contract_end_date)));
                $contract_end_month = (int)(date('m', strtotime($contract_end_date)));
                $contract_end_day = (int)(date('d', strtotime($contract_end_date)));
                if ($contract_end_date != '') {
                    $orderSheet->setCellValueByColumnAndRow(6, $row, $contract_end_year . '/' . $contract_end_month . '/' . $contract_end_day);  // F
                }
                $product_name = $order['product']['name'] ?? '';        // 产品名称
                $orderSheet->setCellValueByColumnAndRow(7, $row, $product_name);      // G
                $long_time = $order['product']['long_time'];       // 投资期限
                $orderSheet->setCellValueByColumnAndRow(8, $row, $long_time);      // H
                $order_money = $order['order_money'];      // 投资金额（元）
                $orderSheet->setCellValueByColumnAndRow(9, $row, $order_money);      // I
                $results_money = $order['results_money'];      // 标准金额
                $orderSheet->setCellValueByColumnAndRow(10, $row, $results_money);      // J
                $rate = $order['product']['rate'];             // 年化收益率利率
                $orderSheet->setCellValueByColumnAndRow(13, $row, $rate);     // M
                $month_money = $order_money * $rate / 1200;
                $orderSheet->setCellValueByColumnAndRow(12, $row, round($month_money, 2));      // L
                $orderSheet->setCellValueByColumnAndRow(11, $row, round($month_money, 0));     // K
                $results_rate = $order['product']['results_rate'] / 100;
                $orderSheet->setCellValueByColumnAndRow(14, $row, $results_rate);       // N
                $is_first = $order['is_first'];
                if ($is_first == 1) {
                    $orderSheet->setCellValueByColumnAndRow(15, $row, '新客户');     // O
                    $orderSheet->setCellValueByColumnAndRow(16, $row, '新签');          // P
                } elseif ($is_first == 2) {
                    $orderSheet->setCellValueByColumnAndRow(15, $row, '老客户');     // O
                    $orderSheet->setCellValueByColumnAndRow(16, $row, '新签');          // P
                } else {
                    $orderSheet->setCellValueByColumnAndRow(15, $row, '老客户');     // O
                    $orderSheet->setCellValueByColumnAndRow(16, $row, '续签');          // P
                }
                $renewal_contract_number = $order['renewal_contract_number'] ?? '';      // 续期合同号
                $orderSheet->setCellValueByColumnAndRow(17, $row, $renewal_contract_number);     // Q
                $user_card_id = $order['user']['id_number'] ?? '';            // 投资人身份证号
                $orderSheet->setCellValueByColumnAndRow(18, $row, $user_card_id . "\t");           // R
                $user_birthday = $order['user']['birthday'] ?? '';            // 投资人生日
                $orderSheet->setCellValueByColumnAndRow(19, $row, date('Y/m/d', strtotime($user_birthday)));      // S
                $bank_card = $order['bank_card']['bank_num'] ?? '';      // 回款银行卡号
                $bank_card_open = $order['bank_card']['bank_sum_name'] ?? '';            // 开户行名称
                $orderSheet->setCellValueByColumnAndRow(20, $row, $bank_card_open);         // T
                $orderSheet->setCellValueByColumnAndRow(21, $row, $bank_card . "\t");         // U
                $branch_three = $order['regional_manage_id'] ?? '';
                $branch_four = $order['department_manage_id'] ?? '';
                $branch_five = $order['sales_manage_id'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(22, $row, $branch_three);      // V
                $orderSheet->setCellValueByColumnAndRow(23, $row, $branch_four);           // W
                $orderSheet->setCellValueByColumnAndRow(24, $row, $branch_five);       // X
                $branch_this = $order['branch']['name'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(25, $row, $branch_this);                 // Y归属部门
                $worker_name = $order['worker']['name'] ?? '';
                $worker_num = $order['worker']['contactNum'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(26, $row, $worker_name);                   // Z
                $orderSheet->setCellValueByColumnAndRow(27, $row, $worker_num . "\t");       // AA
                $branch_five = $order['vice_president_id'] ?? '';
                $orderSheet->setCellValueByColumnAndRow(28, $row, $branch_five);                 // AB二级
                switch ($order['order_status']) {
                    case -1:
                        $order_statu = '草稿';
                        break;
                    case 1:
                        $order_statu = '待付款';
                        break;
                    case 2:
                        $order_statu = '审核中';
                        break;
                    case 3:
                        $order_statu = '待生效';
                        break;
                    case 4:
                        $order_statu = '已生效';
                        break;
                    case 5:
                        $order_statu = '拒绝';
                        break;
                    case 6:
                        $order_statu = '已结束';
                        break;
                    case 7:
                        $order_statu = '废弃';
                        break;
                    case 8:
                        $order_statu = '提前赎回';
                        break;
                    default:
                        $order_statu = '';
                        break;
                }
                $orderSheet->setCellValueByColumnAndRow(29, $row, $order_statu);                 // AC
                $sms_status = $order['sms_status'] == 0 ? '未发送' : '已发送';
                $orderSheet->setCellValueByColumnAndRow(30, $row, $sms_status);                   // AD
                $row = $row + 1;
            }
            $filename = 'O' . time() . '.xlsx';
            $writer = new Xlsx($spreadsheet);
            $path = public_path('excel/' . date("Ymd"));
            File::makeDirectory($path, 0777, true, true);
            $writer->save('../public/excel/' . date("Ymd") . '/' . $filename);
            unset($spreadsheet);
            $exists = Storage::disk('new_ftp')->put('htdocs/excel_files/' . date("Ymd") . '/' . $filename, file_get_contents('../public/excel/' . date("Ymd") . '/' . $filename));
            $file_url = env("FTP2_URL") . 'excel_files/' . date("Ymd") . '/' . $filename;
            $file_path = 'excel_files/' . date("Ymd") . '/' . $filename;
            if ($exists) {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 1;
                $excel_records->file_url = $file_url;
                $excel_records->file_path = $file_path;
                $excel_records->save();
            } else {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 2;
                $excel_records->save();
            }
        } catch (\Throwable $th) {
            $excel_records = ExcelFile::find($excel_record_id);
            $excel_records->is_success = 2;
            $excel_records->save();
        }
    }

    public static function getOtherOrderRecode($where, $order_status, $time_chose, $excel_record_id, $branch_list)
    {
        try {
            $order_list = Order::where($where)->whereIn('order_status', $order_status)->whereIn('branch_id', $branch_list)->with('branch')->with('worker')->with('product')->with('user')->with('bank_card.bank')->with('families_money_records')->get();
            $now_date = date('Y-m-d', time());
            $spreadsheet = new Spreadsheet();
            $orderSheet = $spreadsheet->getActiveSheet();
            // 设置工作表名称
            $orderSheet->setTitle('订单表');
            // 设置表头
            $orderSheet->setCellValueByColumnAndRow(1, 1, '投资日期');      // A
            $orderSheet->setCellValueByColumnAndRow(2, 1, '合同号');       // B
            $orderSheet->setCellValueByColumnAndRow(3, 1, '续期合同号');     // C
            $orderSheet->setCellValueByColumnAndRow(4, 1, '投资人名称');     // D
            $orderSheet->setCellValueByColumnAndRow(5, 1, '投资人联系方式');     // E
            $orderSheet->setCellValueByColumnAndRow(6, 1, '投资金额');      // F
            $orderSheet->setCellValueByColumnAndRow(7, 1, '标准金额');      // G
            $orderSheet->setCellValueByColumnAndRow(8, 1, '投资期限');      // H
            $orderSheet->setCellValueByColumnAndRow(9, 1, '投资方式');      // I
            $orderSheet->setCellValueByColumnAndRow(10, 1, '到账银行');      // J
            $orderSheet->setCellValueByColumnAndRow(11, 1, '到账日期');     // K
            $orderSheet->setCellValueByColumnAndRow(12, 1, '手续费');      // L
            $orderSheet->setCellValueByColumnAndRow(13, 1, '结算方式');     // M
            $orderSheet->setCellValueByColumnAndRow(14, 1, '利率（年化）');       // N
            $orderSheet->setCellValueByColumnAndRow(15, 1, '存量/转续/新单');     // O
            $orderSheet->setCellValueByColumnAndRow(16, 1, '到期时间');          // P
            $orderSheet->setCellValueByColumnAndRow(17, 1, '投资人身份证号码');     // Q
            $orderSheet->setCellValueByColumnAndRow(18, 1, '回款银行账号');           // R
            $orderSheet->setCellValueByColumnAndRow(19, 1, '回款银行名称');      // S
            $orderSheet->setCellValueByColumnAndRow(20, 1, '回款银行开户行');         // T
            $orderSheet->setCellValueByColumnAndRow(21, 1, '理财经理');         // T
            $orderSheet->setCellValueByColumnAndRow(22, 1, '团队经理');         // U
            $orderSheet->setCellValueByColumnAndRow(23, 1, '营业部经理');      // V
            $orderSheet->setCellValueByColumnAndRow(24, 1, '分公司经理');           // W
            $orderSheet->setCellValueByColumnAndRow(25, 1, '城市大区经理');       // X
            $orderSheet->setCellValueByColumnAndRow(26, 1, '副总裁');                 // Y
            $orderSheet->setCellValueByColumnAndRow(27, 1, '总裁');                   // Z
            $orderSheet->setCellValueByColumnAndRow(28, 1, '代理公司/分公司');       // AA
            $orderSheet->setCellValueByColumnAndRow(29, 1, '是否在职');                 // AB
            $orderSheet->setCellValueByColumnAndRow(30, 1, '备注1');                 // AC
            $orderSheet->setCellValueByColumnAndRow(31, 1, '订单状态');                   // AD
            $row = 2;
            foreach ($order_list as $order) {
                if ($row > 20000) {
                    break;
                }
                $investment_data = $time_chose == 1 ? $order['valid_time'] : $order['sign_time'];  // 投资日期
                $contract_no = $order['contract_no'] ?? '';      // 合同号
                $renewal_contract_number = $order['renewal_contract_number'] ?? '';      // 续期合同号
                $user_name = $order['user']['name'];       // 投资人姓名
                $user_num = $order['user']['contact_manner'];       // 投资人联系方式
                $order_money = $order['order_money'];      // 投资金额（元）
                $results_money = $order['results_money'];      // 标准金额
                $long_time = $order['product']['long_time'];       // 投资期限
                // $investment_way = $order['investment_way'] ?? '';        // 投资方式
                $investment_way = $order['investment_way'] ?? 1;        // 投资状态  1常规  2通联待付款 3通联已付款
                if ($investment_way == 1) {
                    $pay_type_lists = $order['families_money_records'];
                    if (count($pay_type_lists) > 0) {
                        $pay_type_list = [];
                        foreach ($pay_type_lists as $a_pay_type) {
                            if ($a_pay_type['type'] == 1) {
                                if (!in_array($a_pay_type['pay_type'], $pay_type_list)) {
                                    $pay_type_list[] = $a_pay_type['pay_type'];
                                }
                            }
                        }
                        $pay_type_str = implode('/', $pay_type_list);
                        $pay_type_str = str_replace('1', '银行转账', $pay_type_str);
                        $pay_type_str = str_replace('2', 'pos刷卡', $pay_type_str);
                        $pay_type_str = str_replace('3', '转存', $pay_type_str);
                        $orderSheet->setCellValueByColumnAndRow(9, $row, $pay_type_str);
                    } else {
                        $orderSheet->setCellValueByColumnAndRow(9, $row, null);
                    }
                } elseif ($investment_way == 2) {
                    $orderSheet->setCellValueByColumnAndRow(9, $row, '通联待付款');
                } elseif ($investment_way == 3) {
                    $orderSheet->setCellValueByColumnAndRow(9, $row, '通联已付款');
                }
                if (isset($order['families_id'])) {
                    if ($order['families_id'] == 1) {
                        $orderSheet->setCellValueByColumnAndRow(10, $row, '民生银行');
                    } elseif ($order['families_id'] == 2) {
                        $orderSheet->setCellValueByColumnAndRow(10, $row, '建设银行');
                    } elseif ($order['families_id'] == 3) {
                        $orderSheet->setCellValueByColumnAndRow(10, $row, '民生银行/建设银行');
                    }
                }
                $account_time = $order['account_time'] ?? '';        // 到账日期
                $course_money = $order['course_money'] ?? 0;        // 手续费
                $contract_end_date = $order['contract_end_date'] ?? '';   // 到期时间
                $pay_types = $order['product']['pay_type'] ?? '';                      // 结算方式
                if ($pay_types == 4) {
                    if ($order['contract_end_date'] > $now_date) {
                        $pay_type = '一次性付';
                    } else {
                        $pay_type = '一次性付已到期';
                    }
                } else {
                    if ($order['contract_end_date'] > $now_date) {
                        $pay_long = $order['product']['pay_long'];
                        switch ($pay_long) {
                            case 1:
                                $pay_type = '月付';
                                break;
                            case 3:
                                $pay_type = '季付';
                                break;
                            case 12:
                                $pay_type = '年付';
                                break;
                            default:
                                $pay_type = '一次性付';
                        }
                    } else {
                        $pay_long = $order['product']['pay_long'];
                        switch ($pay_long) {
                            case 1:
                                $pay_type = '月付已到期';
                                break;
                            case 3:
                                $pay_type = '季付已到期';
                                break;
                            case 12:
                                $pay_type = '年付已到期';
                                break;
                            default:
                                $pay_type = '一次性付已到期';
                        }
                    }
                }
                $rate = $order['product']['rate'] . '%';             // 利率
                switch ($order['is_first']) {
                    case 1:
                        $is_first = '新单';
                        break;
                    case 2:
                        $is_first = '存量';
                        break;
                    case 3:
                        $is_first = '转续';
                        break;
                    default:
                        $is_first = '';
                }                       // 订单类型
                $user_card_id = $order['user']['id_number'] ?? '';            // 投资人身份证号
                $bank_card = $order['bank_card']['bank_num'] ?? '';      // 回款银行卡号
                $bank_card_open = $order['bank_card']['bank_sum_name'] ?? '';            // 开户行名称
                $bank_name = $order['bank_card']['bank']['bank_name'] ?? '';            // 银行名称
                $is_job = $order['worker']['user_status'] == 1 ? '在职' : '离职';
                $branch_name = $order['branch']['name'];                           // 代理公司
                $orderSheet->setCellValueByColumnAndRow(21, $row, $order['wealth_manage_id'] ?? '');
                $orderSheet->setCellValueByColumnAndRow(22, $row, $order['group_manage_id'] ?? '');
                $orderSheet->setCellValueByColumnAndRow(23, $row, $order['sales_manage_id'] ?? '');
                $orderSheet->setCellValueByColumnAndRow(24, $row, $order['department_manage_id'] ?? '');
                $orderSheet->setCellValueByColumnAndRow(25, $row, $order['regional_manage_id'] ?? '');
                $orderSheet->setCellValueByColumnAndRow(26, $row, $order['vice_president_id'] ?? '');
                $orderSheet->setCellValueByColumnAndRow(27, $row, $order['president_id'] ?? '');
                $remark = $order['remark'] ?? '';
                if ($investment_data) {
                    $orderSheet->setCellValueByColumnAndRow(1, $row, date('Y-m-d', strtotime($investment_data)));
                }
                $orderSheet->setCellValueByColumnAndRow(2, $row, $contract_no . "\t");
                $orderSheet->setCellValueByColumnAndRow(3, $row, $renewal_contract_number . "\t");
                $orderSheet->setCellValueByColumnAndRow(4, $row, $user_name);
                $orderSheet->setCellValueByColumnAndRow(5, $row, $user_num);
                $orderSheet->setCellValueByColumnAndRow(6, $row, $order_money);
                $orderSheet->setCellValueByColumnAndRow(7, $row, $results_money);
                $orderSheet->setCellValueByColumnAndRow(8, $row, $long_time);
                if ($account_time) {
                    $orderSheet->setCellValueByColumnAndRow(11, $row, date('Y-m-d', strtotime($account_time)));
                }
                $orderSheet->setCellValueByColumnAndRow(12, $row, $course_money);
                $orderSheet->setCellValueByColumnAndRow(13, $row, $pay_type);
                $orderSheet->setCellValueByColumnAndRow(14, $row, $rate);
                $orderSheet->setCellValueByColumnAndRow(15, $row, $is_first);
                if ($contract_end_date) {
                    $orderSheet->setCellValueByColumnAndRow(16, $row, date('Y-m-d', strtotime($contract_end_date)));
                }
                $orderSheet->setCellValueByColumnAndRow(17, $row, $user_card_id . "\t");
                $orderSheet->setCellValueByColumnAndRow(18, $row, $bank_card . "\t");
                $orderSheet->setCellValueByColumnAndRow(20, $row, $bank_card_open);
                $orderSheet->setCellValueByColumnAndRow(19, $row, $bank_name);
                $orderSheet->setCellValueByColumnAndRow(28, $row, $branch_name);
                $orderSheet->setCellValueByColumnAndRow(29, $row, $is_job);
                $orderSheet->setCellValueByColumnAndRow(30, $row, $remark);
                switch ($order['order_status']) {
                    case -1:
                        $order_statu = '草稿';
                        break;
                    case 1:
                        $order_statu = '待付款';
                        break;
                    case 2:
                        $order_statu = '审核中';
                        break;
                    case 3:
                        $order_statu = '待生效';
                        break;
                    case 4:
                        $order_statu = '已生效';
                        break;
                    case 5:
                        $order_statu = '拒绝';
                        break;
                    case 6:
                        $order_statu = '已结束';
                        break;
                    default:
                        $order_statu = '';
                        break;
                }
                $orderSheet->setCellValueByColumnAndRow(31, $row, $order_statu);
                $row = $row + 1;
            }
            $filename = 'O' . time() . '.xlsx';
            $writer = new Xlsx($spreadsheet);
            $path = public_path('excel/' . date("Ymd"));
            File::makeDirectory($path, 0777, true, true);
            $writer->save('../public/excel/' . date("Ymd") . '/' . $filename);
            unset($spreadsheet);
            $exists = Storage::disk('new_ftp')->put('htdocs/excel_files/' . date("Ymd") . '/' . $filename, file_get_contents('../public/excel/' . date("Ymd") . '/' . $filename));
            $file_url = env("FTP2_URL") . 'excel_files/' . date("Ymd") . '/' . $filename;
            $file_path = 'excel_files/' . date("Ymd") . '/' . $filename;
            if ($exists) {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 1;
                $excel_records->file_url = $file_url;
                $excel_records->file_path = $file_path;
                $excel_records->save();
            } else {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 2;
                $excel_records->save();
            }
        } catch (\Throwable $th) {
            $excel_records = ExcelFile::find($excel_record_id);
            $excel_records->is_success = 2;
            $excel_records->save();
        }
    }
}
